import mysql.connector


def get_tables(cnx, database):
    # 查询所有的数据表
    cursor = cnx.cursor()
    query = (f'''
        select TABLE_NAME, TABLE_COMMENT from information_schema.TABLES
        where TABLE_SCHEMA = '{database}'
        ''')
    cursor.execute(query)

    tables = []
    for row in cursor:
        tables.append({'table_name': row[0], 'table_comment': row[1]})
    return tables


def get_column(cnx, database, table_name):
    cursor = cnx.cursor()
    query = (f'''
        select COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
        from information_schema.COLUMNS
        where TABLE_NAME = '{table_name}'
        and TABLE_SCHEMA = '{database}'
        ''')
    cursor.execute(query)
    columns = []
    for row in cursor:
        column_key = '' if row[2] is None else row[2]
        columns.append({'column_name': row[0], 'column_type': row[1], 'column_key': column_key, 'is_nullable': row[3],
                        'column_default': row[4]})
    columns = sorted(columns, key=lambda c: c['column_key'])
    columns.reverse()
    return columns


def table_compare(table1_names, table2_names):
    tab1_gt_tab2 = table1_names.difference(table2_names)
    tab2_gt_tab1 = table2_names.difference(table1_names)
    both_exists = table1_names & table2_names
    return {'t1_gt_t2': tab1_gt_tab2, 't2_gt_t1': tab2_gt_tab1, 'both_exists': both_exists}


def table_structure_compare(cnx, db1, db2, tables):
    result = []

    for tab in tables:
        db1_cols = get_column(cnx, db1, tab)
        db2_cols = get_column(cnx, db2, tab)

        db1_col_names = set(col["column_name"] for col in db1_cols)
        db2_col_names = set(col["column_name"] for col in db2_cols)
        both_col_names = db1_col_names & db2_col_names

        db1_col_map = {col["column_name"]: col for col in db1_cols}
        db2_col_map = {col["column_name"]: col for col in db2_cols}

        # 新增的字段
        add_col_names = db2_col_names.difference(db1_col_names)
        # 删除的字段
        drop_col_names = db1_col_names.difference(db2_col_names)

        # 更新的字段
        update_cols = []
        for col_name in both_col_names:
            db1_col = db1_col_map.get(col_name)
            db2_col = db2_col_map.get(col_name)

            if db1_col == db2_col:
                continue
            update_cols.append({'origin': db1_col, 'new': db2_col})

        if len(drop_col_names) == 0 and len(add_col_names) == 0 and len(update_cols) == 0:
            continue

        result.append({'tab': tab, 'drop_cols': drop_col_names, 'add_cols': add_col_names, 'update_cols': update_cols})

    return result


def print_file(db1_tab_names, db2_tab_names, tab_diff, tab_structure_diff):
    file = open("表结构比较.md", "w", encoding='utf-8')

    tab1_gt_tab2 = tab_diff.get('t1_gt_t2')
    tab2_gt_tab1 = tab_diff.get('t2_gt_t1')
    tab1_gt_tab2_str = '，'.join(tab1_gt_tab2)
    tab2_gt_tab1_str = '，'.join(tab2_gt_tab1)

    update_table_names = set(tab["tab"] for tab in tab_structure_diff)
    update_table_names_str = '，'.join(update_table_names)

    # 表总体比较
    file.write('## 1. 表总体比较 \n\n')
    file.write(f'* db1共有{len(db1_tab_names)}张表，db2共有{len(db2_tab_names)}张表 \n')
    file.write(f'* 删除{len(tab1_gt_tab2)}张表：{tab1_gt_tab2_str} \n')
    file.write(f'* 新增{len(tab2_gt_tab1)}张表：{tab2_gt_tab1_str} \n')
    file.write(f'* 更新{len(tab_structure_diff)}张表：{update_table_names_str} \n')
    file.write('\n\n')

    # 同时存在的表，表结构的详细变化
    file.write(f'## 2. 表详细比较 \n\n')
    for index, tab in enumerate(tab_structure_diff):
        tab_name = tab.get('tab')
        drop_cols = tab.get('drop_cols')
        add_cols = tab.get('add_cols')
        update_cols = tab.get('update_cols')

        serial_num = index + 1
        child_serial_num = 1

        file.write(f'### {serial_num}. {tab_name} \n\n')

        if len(drop_cols) != 0:
            file.write(f'#### {serial_num}.{child_serial_num}. 删除字段 \n\n')
            file.write(f'{"，".join(drop_cols)} \n\n')
            child_serial_num += 1

        if len(add_cols) != 0:
            file.write(f'#### {serial_num}.{child_serial_num}. 新增字段 \n\n')
            file.write(f'{"，".join(add_cols)} \n\n')
            child_serial_num += 1

        if len(update_cols) != 0:
            file.write(f'#### {serial_num}.{child_serial_num}. 更新字段 \n\n')

            for index2, update_col in enumerate(update_cols):
                file.write(
                    f'##### {serial_num}.{child_serial_num}.{index2 + 1}. {update_col.get("new").get("column_name")} \n\n')
                file.write('| | 字段名 | 数据类型 | 键 | 为空 | 默认值 | 字段描述 | \n')
                file.write('| --- | --- | --- | --- | --- | --- | --- | \n')
                file.write(f'| 原字段 {get_col_line_str(update_col.get("origin"))} \n')
                file.write(f'| 新字段 {get_col_line_str(update_col.get("new"))} \n\n')

    file.close()


def get_col_line_str(col):
    column_type = col.get('column_type').decode('utf-8')

    column_default = col.get('column_default')
    if type(column_default) is bytes:
        column_default = column_default.decode('utf-8')

    column_comment = str(col.get("column_comment"))
    if '\r' in column_comment:
        column_comment = column_comment.replace('\r', '<br>').replace('\n', '')
    else:
        column_comment = column_comment.replace('\n', '<br/>').replace('\r', '')

    return f'| {col.get("column_name")} | {column_type} | {col.get("column_key")} ' \
           f'| {col.get("is_nullable")} | {column_default} | {column_comment} |'


if __name__ == '__main__':
    # 数据库连接
    cnx = mysql.connector.connect(user='root', password='12345678', host='127.0.0.1',
                                  database='information_schema')
    # 原版本数据库
    db1 = 'my_db1'
    # 新版本数据库
    db2 = 'my_db1'

    # 获取所有表
    db1_tables = get_tables(cnx, db1)
    db2_tables = get_tables(cnx, db2)

    # 提取表名称
    db1_tab_names = set(tab["table_name"] for tab in db1_tables)
    db2_tab_names = set(tab["table_name"] for tab in db2_tables)

    # 比较表
    tab_diff = table_compare(db1_tab_names, db2_tab_names)

    # 比较每张表的表结构
    tab_structure_diff = table_structure_compare(cnx, db1, db2, tab_diff.get('both_exists'))

    # 输出文件
    print_file(db1_tab_names, db2_tab_names, tab_diff, tab_structure_diff)
